﻿// ************************************************* //
// AccessDataBase.cs
// Author: Chris Wu
// Email: cwu@dataio.com
// ------------------------------------ //
// version 1.0 - Initial Create
// date: March 25th, 2010
// ------------------------------------ //
//
// ************************************************* //

using System;
using System.Data;
using System.Data.OleDb;

namespace Common
{
    /// <summary>
    /// Description of AccessDataBase.
    /// </summary>
	public class AccessDataBase
	{
        //private static string constr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\\..\\AdapterDatabase.mdb";
        private static string constr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NewAdapterDB.mdb";
        //private static string constr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=QueryDB.mdb";
        //private static string addr = @"\\master\home\wuc\sharedfiles\AdapterDatabase.mdb";
        //private static string constr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + addr;

        // AccessDataBase Constructor
        public AccessDataBase()
        { }

        // Load AdapterListTable
        public AdapterListTableElement LoadAdapterListTable(int adptrid)
        {
            AdapterListTableElement alte = new AdapterListTableElement();
            alte.AdapterId = adptrid;

            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            OleDbCommand odcom = odconn.CreateCommand();
            odcom.CommandText = "select * from AdapterListTable";
            OleDbDataReader oddrd = odcom.ExecuteReader();

            bool ret = false;

            while (oddrd.Read())
            {
                if (int.Parse(oddrd[oddrd.GetName(0)].ToString()) == adptrid)
                {
                    alte.PinMapId = int.Parse(oddrd[oddrd.GetName(1)].ToString());
                    alte.SocketId = oddrd[oddrd.GetName(2)].ToString();
                    alte.AdapterName  = oddrd[oddrd.GetName(3)].ToString();
                    alte.SocketNum = int.Parse(oddrd[oddrd.GetName(4)].ToString());
                    alte.PinOneOri = oddrd[oddrd.GetName(5)].ToString();
                    alte.PackageInfo = oddrd[oddrd.GetName(6)].ToString();

                    ret = true;
                    break;
                }
            }

            oddrd.Close();
            odconn.Close();

            if (!ret)
            {
                return null;
            }

            return alte;
        }

        // Load PackageTypeTable
        public PackageTypeElement LoadPackageTypeTable(string packtype)
        {
            PackageTypeElement pte = new PackageTypeElement();
            pte.PackageType = packtype;

            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            OleDbCommand odcom = odconn.CreateCommand();
            odcom.CommandText = "select * from PackageTypeTable";
            OleDbDataReader oddrd = odcom.ExecuteReader();

            while (oddrd.Read())
            {
                if (packtype == oddrd[oddrd.GetName(0)].ToString())
                {
                    pte.PackageName = oddrd[oddrd.GetName(1)].ToString();
                    break;
                }
            }

            oddrd.Close();
            odconn.Close();

            return pte;
        }

        // Load PinMapListTable
        public PinMapListElement LoadPinMapListTable(int pinmapid)
        {
            PinMapListElement pmle = new PinMapListElement();
            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            OleDbCommand odcom = odconn.CreateCommand();
            odcom.CommandText = "select * from PinMapListTable";
            OleDbDataReader oddrd = odcom.ExecuteReader();
            bool ret = false;

            while (oddrd.Read())
            {
                if (int.Parse(oddrd[oddrd.GetName(0)].ToString()) == pinmapid)
                {
                    pmle.PinMapId = int.Parse(oddrd[oddrd.GetName(0)].ToString());
                    pmle.PackageName = oddrd[oddrd.GetName(1)].ToString();
                    pmle.RowNum = int.Parse(oddrd[oddrd.GetName(2)].ToString());
                    pmle.ColumnNum = int.Parse(oddrd[oddrd.GetName(3)].ToString());
                    pmle.TotalPins = int.Parse(oddrd[oddrd.GetName(4)].ToString());
                    
                    ret = true;
                    break;
                }
            }

            oddrd.Close();
            odconn.Close();

            if (!ret)
            {
                return null;
            }

            return pmle;
        }

        // Load PinOutListTable
        public PinOutListElement LoadPinOutListTable(int pinmapid, string loc)
        {
            PinOutListElement pole = new PinOutListElement();
            pole.PinMapId = pinmapid;
            pole.Position = loc;
            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            OleDbCommand odcom = odconn.CreateCommand();
            odcom.CommandText = "select * from PinOutListTable";
            OleDbDataReader oddrd = odcom.ExecuteReader();
            bool ret = false;

            while (oddrd.Read())
            {
                if (int.Parse(oddrd[oddrd.GetName(0)].ToString()) == pinmapid &&
                    oddrd[oddrd.GetName(1)].ToString() == loc)
                {
                    pole.Label = oddrd[oddrd.GetName(2)].ToString();
                    pole.DevicePin = oddrd[oddrd.GetName(3)].ToString();
                    
                    ret = true;
                    break;
                }
            }

            oddrd.Close();
            odconn.Close();

            if (!ret)
            {
                return null;
            }

            return pole;
        }

        // search NoteListTable
        public int SearchNoteListTable(int pinmapid, ref NoteListElement[] nle)
        {
            int i = 0;
            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            OleDbCommand odcom = odconn.CreateCommand();
            odcom.CommandText = "select * from NoteListTable";
            OleDbDataReader oddrd = odcom.ExecuteReader();
            while (oddrd.Read())
            {
                if (pinmapid == int.Parse(oddrd[oddrd.GetName(0)].ToString()))
                {
                    nle[i] = new NoteListElement();
                    nle[i].PinMapId = pinmapid;
                    nle[i].NoteId = int.Parse(oddrd[oddrd.GetName(1)].ToString());
                    nle[i].Notes = oddrd[oddrd.GetName(2)].ToString();
                    i++;
                }
            }

            oddrd.Close();
            odconn.Close();

            return i;
        }

        // search ImageListTable
        public int SearchImageListTable(int pinmapid, ref ImageListElement[] ile)
        {
            int i = 0;
            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            OleDbCommand odcom = odconn.CreateCommand();
            odcom.CommandText = "select * from ImageListTable";
            OleDbDataReader oddrd = odcom.ExecuteReader();
            while (oddrd.Read())
            {
                if (pinmapid == int.Parse(oddrd[oddrd.GetName(0)].ToString()))
                {
                    ile[i] = new ImageListElement();
                    ile[i].PinMapId = pinmapid;
                    ile[i].ImageId = int.Parse(oddrd[oddrd.GetName(1)].ToString());
                    ile[i].ImagesByte = (byte[])oddrd[oddrd.GetName(2)];
                    i++;
                }
            }

            oddrd.Close();
            odconn.Close();

            return i;
        }

        // Search AdapterListTable
        public AdapterListTableElement[] SearchAdapterListTable(int adptrid, int num)
        {
            AdapterListTableElement[] alte = new AdapterListTableElement[num];
            int i = 0;

            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            OleDbCommand odcom = odconn.CreateCommand();
            odcom.CommandText = "select * from AdapterListTable";
            OleDbDataReader oddrd = odcom.ExecuteReader();
            bool ret = false;

            while (oddrd.Read())
            {
                if (adptrid == int.Parse(oddrd[oddrd.GetName(0)].ToString()))
                {
                    alte[i] = new AdapterListTableElement();
                    alte[i].AdapterId = adptrid;
                    alte[i].PinMapId = int.Parse(oddrd[oddrd.GetName(1)].ToString());
                    alte[i].SocketId = oddrd[oddrd.GetName(2)].ToString();
                    alte[i].AdapterName = oddrd[oddrd.GetName(3)].ToString();
                    alte[i].SocketNum = int.Parse(oddrd[oddrd.GetName(4)].ToString());
                    alte[i].PinOneOri = oddrd[oddrd.GetName(5)].ToString();
                    alte[i].PackageInfo = oddrd[oddrd.GetName(6)].ToString();
                    ret = true;
                    i++;
                }
            }

            oddrd.Close();
            odconn.Close();

            if (!ret)
            {
                return null;
            }

            return alte;
        }

        //// Save record to AdapterListTable
        //public void SaveRecordAdapterListTable(AdapterListTableElement alte)
        //{
        //    //OleDbConnection odconn = new OleDbConnection(constr);
        //    //odconn.Open();

        //    //string icstr = "insert into AdapterListTable(AdapterID, AdapterName, PackageType, TotalPins, RowNum, ColumnNum) Values('" +
        //    //                alte.AdapterID.ToString() + "', '" + alte.AdapterName + "', '" + alte.PackagaType + "', '" + alte.TotalPins.ToString() +
        //    //                "', '" + alte.RowNum.ToString() + "', '" + alte.ColumnNum.ToString() + "')";
        //    //OleDbCommand odcom = new OleDbCommand(icstr, odconn);
        //    //odcom.ExecuteNonQuery();

        //    //odconn.Close();
        //}

        // query exist PinOutListTable
        public bool QueryExistPinOutListTable(int pinmapid, string position)
        {
            bool ret = false;
            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            OleDbCommand odcom = odconn.CreateCommand();
            odcom.CommandText = "select * from PinOutListTable";
            OleDbDataReader oddrd = odcom.ExecuteReader();

            while (oddrd.Read())
            {
                if (int.Parse(oddrd[oddrd.GetName(0)].ToString()) == pinmapid &&
                    oddrd[oddrd.GetName(1)].ToString() == position)
                {
                    ret = true;
                    break;
                }
            }

            oddrd.Close();
            odconn.Close();

            return ret;
        }

        // insert record to PinOutListTable
        public void InsertRecPinOutListTable(PinOutListElement pole)
        {
            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            string icstr = "insert into PinoutListTable(PinMapId, [Position], [Label], [DevicePin]) Values('" +
                            pole.PinMapId.ToString() + "', '" + pole.Position + "', '" + pole.Label +
                            "', '" + pole.DevicePin + "')";
            OleDbCommand odcomm = new OleDbCommand(icstr, odconn);
            odcomm.ExecuteNonQuery();

            odconn.Close();
        }

        //// update record to AdapterListTable
        //public void UpdateRecordAdapterListTable(AdapterListTableElement alte)
        //{
        //    //OleDbConnection odconn = new OleDbConnection(constr);
        //    //odconn.Open();

        //    //string udstr = "update AdapterListTable set AdapterName='" + alte.AdapterName + "', PackageType='" +
        //    //                alte.PackagaType + "', TotalPins=" + alte.TotalPins + ", RowNum=" +
        //    //                alte.RowNum + ", ColumnNum=" + alte.ColumnNum +
        //    //                " where AdapterID=" + alte.AdapterID;
        //    //OleDbCommand odcom = new OleDbCommand(udstr, odconn);
        //    //odcom.ExecuteNonQuery();

        //    //odconn.Close();
        //}

        // update record to PinOutList
        public void UpdateRecPinoutListTable(PinOutListElement pole)
        {
            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            string udstr = "update PinoutListTable set [Label]='" + pole.Label + "', [DevicePin]='" + pole.DevicePin + 
                            "' where PinMapId=" + pole.PinMapId + "and [Position]='" + pole.Position + "'";
            OleDbCommand odcom = new OleDbCommand(udstr, odconn);
            odcom.ExecuteNonQuery();

            odconn.Close();
        }

        // query exist PinMapListTable
        public bool QueryExistPinMapListTable(int pinmapid)
        {
            bool ret = false;
            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            OleDbCommand odcom = odconn.CreateCommand();
            odcom.CommandText = "select * from PinMapListTable";
            OleDbDataReader oddrd = odcom.ExecuteReader();

            while (oddrd.Read())
            {
                if (int.Parse(oddrd[oddrd.GetName(0)].ToString()) == pinmapid)
                {
                    ret = true;
                    break;
                }
            }

            oddrd.Close();
            odconn.Close();

            return ret;
        }

        // update PinMapListTable
        public void UpdateRecPinMapListTable(PinMapListElement pmle)
        {
            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            string udstr = "update PinMapListTable set [PackageName]='" + pmle.PackageName + "', RowNum=" +
                            pmle.RowNum + ", ColumnNum=" + pmle.ColumnNum + ", TotalPins=" + pmle.TotalPins +
                            " where PinMapId=" + pmle.PinMapId;
            OleDbCommand odcom = new OleDbCommand(udstr, odconn);
            odcom.ExecuteNonQuery();

            odconn.Close();
        }

        // insert PinMapListTable
        public int InsertRecPinMapListTable(PinMapListElement pmle)
        {
            // insert a new pmle and return the pinmapid
            int pinmapid = 0;

            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            string icstr = "insert into PinMapListTable(PackageName, RowNum, ColumnNum, TotalPins) Values('" +
                            pmle.PackageName + "', '" + pmle.RowNum.ToString() + "', '" + pmle.ColumnNum.ToString() +
                            "', '" + pmle.TotalPins.ToString() + "')";
            OleDbCommand odcom = new OleDbCommand(icstr, odconn);
            odcom.ExecuteNonQuery();
            odcom.CommandText = @"select @@identity";
            pinmapid = int.Parse(odcom.ExecuteScalar().ToString());

            odconn.Close();

            return pinmapid;
        }

        // query NoteListTable
        public bool QueryExistNoteListTable(int pinmapid)
        {
            bool ret = false;
            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            OleDbCommand odcom = odconn.CreateCommand();
            odcom.CommandText = "select * from NoteListTable";
            OleDbDataReader oddrd = odcom.ExecuteReader();

            while (oddrd.Read())
            {
                if (int.Parse(oddrd[oddrd.GetName(0)].ToString()) == pinmapid)
                {
                    ret = true;
                    break;
                }
            }

            oddrd.Close();
            odconn.Close();

            return ret;
        }
        
        // delete all records of pinmapid in NoteListTable
        public void DeleteRecNoteListTable(int pinmapid)
        {
            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            string icstr = "delete from NoteListTable where PinMapId=" + pinmapid;
            OleDbCommand odcom = new OleDbCommand(icstr, odconn);
            odcom.ExecuteNonQuery();
            
            odconn.Close();            
        }

        // insert records to NoteListTable
        public void InsertRecNoteListTable(NoteListElement nle)
        {
            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            string icstr = "insert into NoteListTable(PinMapId, NoteId, [Notes]) Values('" +
                            nle.PinMapId.ToString() + "', '" + nle.NoteId.ToString() + "', '" + nle.Notes + "')";
            OleDbCommand odcomm = new OleDbCommand(icstr, odconn);
            odcomm.ExecuteNonQuery();

            odconn.Close();
        }

        // query ImageListTable
        public bool QueryExistImageListTable(int pinmapid)
        {
            bool ret = false;
            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            OleDbCommand odcom = odconn.CreateCommand();
            odcom.CommandText = "select * from ImageListTable";
            OleDbDataReader oddrd = odcom.ExecuteReader();

            while (oddrd.Read())
            {
                if (int.Parse(oddrd[oddrd.GetName(0)].ToString()) == pinmapid)
                {
                    ret = true;
                    break;
                }
            }

            oddrd.Close();
            odconn.Close();

            return ret;
        }

        // delete all records of pinmapid in ImageListTable
        public void DeleteRecImageListTable(int pinmapid)
        {
            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            string icstr = "delete from ImageListTable where PinMapId=" + pinmapid;
            OleDbCommand odcom = new OleDbCommand(icstr, odconn);
            odcom.ExecuteNonQuery();

            odconn.Close();
        }

        // insert records to ImageListTable
        public void InsertRecImageListTable(ImageListElement ile)
        {
            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            //string icstr = "insert into ImageListTable(PinMapId, ImageId, Images) Values('" +
            //                ile.PinMapId.ToString() + "', '" + ile.ImageId.ToString() + "', " + ile.ImagesByte + ")";
            string sql = "insert into ImageListTable(PinMapId, ImageId, Images) Values (?, ?, ?);";
            OleDbParameter[] parameters = new OleDbParameter[3];
            parameters[0] = new OleDbParameter("@PinMapId", OleDbType.Integer);
            parameters[0].Value = ile.PinMapId;
            parameters[1] = new OleDbParameter("@ImageId", OleDbType.Integer);
            parameters[1].Value = ile.ImageId;
            parameters[2] = new OleDbParameter("@Images", System.Data.SqlDbType.Image);
            parameters[2].Value = ile.ImagesByte;
            OleDbCommand odcomm = new OleDbCommand(sql, odconn);
            odcomm.Parameters.AddRange(parameters);
            odcomm.ExecuteNonQuery();

            odconn.Close();
        }

        // query exist records of adapterid in AdapterListTable
        public bool QueryExistAdapterListTable(int adapterid)
        {
            bool ret = false;
            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            OleDbCommand odcom = odconn.CreateCommand();
            odcom.CommandText = "select * from AdapterListTable";
            OleDbDataReader oddrd = odcom.ExecuteReader();

            while (oddrd.Read())
            {
                if (int.Parse(oddrd[oddrd.GetName(0)].ToString()) == adapterid)
                {
                    ret = true;
                    break;
                }
            }

            oddrd.Close();
            odconn.Close();

            return ret;
        }

        // delete records of pinmapid in AdapterListTable
        public void DeleteRecAdapterListTable(int pinmapid)
        {
            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            string icstr = "delete from AdapterListTable where PinMapId=" + pinmapid;
            OleDbCommand odcom = new OleDbCommand(icstr, odconn);
            odcom.ExecuteNonQuery();

            odconn.Close();
        }

        // insert records of pinmapid into AdapterListTable
        public void InsertRecAdapterListTable(AdapterListTableElement alte)
        {
            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            string icstr = "insert into AdapterListTable(AdapterId, PinMapId, [SocketId], [AdapterName], SocketNum, [PinOneOri], [PackageInfo]) Values(" +
                            alte.AdapterId + ", " + alte.PinMapId + ", '" + alte.SocketId + "', '" + alte.AdapterName + "', " +
                            alte.SocketNum + ", '" + alte.PinOneOri + "', '" + alte.PackageInfo + "')";
            OleDbCommand odcomm = new OleDbCommand(icstr, odconn);
            odcomm.ExecuteNonQuery();

            odconn.Close();
        }

        // update AdapterListTable
        public void UpdateRecAdapterListTable(AdapterListTableElement alte)
        {
            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            string udstr = "update AdapterListTable set PinMapId=" + alte.PinMapId + ", [SocketId]='" + alte.SocketId + "', [AdapterName]='" +
                            alte.AdapterName + "', SocketNum=" + alte.SocketNum + ", [PinOneOri]='" + alte.PinOneOri + 
                            "', [PackageInfo]='" + alte.PackageInfo + "' where AdapterId=" + alte.AdapterId;
            OleDbCommand odcom = new OleDbCommand(udstr, odconn);
            odcom.ExecuteNonQuery();

            odconn.Close();
        }

        // query records in HWParamTable
        public bool QueryRecHWParamTable(string socketid)
        {
            bool ret = false;
            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            OleDbCommand odcom = odconn.CreateCommand();
            odcom.CommandText = "select * from HWParamTable";
            OleDbDataReader oddrd = odcom.ExecuteReader();

            while (oddrd.Read())
            {
                if (oddrd[oddrd.GetName(0)].ToString() == socketid)
                {
                    ret = true;
                    break;
                }
            }

            oddrd.Close();
            odconn.Close();
            return ret;
        }

        // insert records into HWParamTable
        public void InsertRecHWParamTable(HWParamTableElement hwpte)
        { 
            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            string icstr = "insert into HWParamTable([SocketId], BodySize, Width, Pitch, BallDiameter, BallHeight, PkgHeight) Values('" + hwpte.SocketId + 
                "', " + hwpte.BodySize + ", " + hwpte.Width + ", " + hwpte.Pitch + ", " +
                hwpte.BallDiameter + ", " + hwpte.BallHeight + ", " + hwpte.PkgHeight + ")";
            OleDbCommand odcomm = new OleDbCommand(icstr, odconn);
            odcomm.ExecuteNonQuery();

            odconn.Close();
        }

        // update HWParamTable
        public void UpdateRecHWParamTable(HWParamTableElement hwpte)
        {
            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            string udstr = "update HWParamTable set BodySize=" + hwpte.BodySize + ", Width=" + 
                hwpte.Width + ", Pitch=" + hwpte.Pitch + ", BallDiameter=" + hwpte.BallDiameter + 
                ", BallHeight=" + hwpte.BallHeight + ", PkgHeight=" + hwpte.PkgHeight + 
                " where [SocketId]='" + hwpte.SocketId + "'";
            OleDbCommand odcom = new OleDbCommand(udstr, odconn);
            odcom.ExecuteNonQuery();

            odconn.Close();
        }

        // insert records into AdapterOptionIdTable
        public void InsertRecAdapterOptionIdTable(AdapterOptionIdElement aoie)
        {
            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            string icstr = "insert into AdapterOptionIdTable(AdapterId, [Position], OptionID) Values(" +
                            aoie.AdapterId + ", '" + aoie.Position + "', " + aoie.OptionId + ")";
            OleDbCommand odcomm = new OleDbCommand(icstr, odconn);
            odcomm.ExecuteNonQuery();

            odconn.Close();
        }

        // update AdapterOptionIdTable
        public void UpdateRecAdapterOptionIDTable(AdapterOptionIdElement aoie)
        {
            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            string udstr = "update AdapterOptionIdTable set OptionId=" + aoie.OptionId + " where AdapterId=" +
                aoie.AdapterId + " and [Position]='" + aoie.Position.ToString() + "'";
            OleDbCommand odcom = new OleDbCommand(udstr, odconn);
            odcom.ExecuteNonQuery();

            odconn.Close();
        }

        // query HWParamTable with adapterid
        public int QueryAdapter4HWParamTable(ref int[] adapterlist, int[] posilist, double[] valist, int num)
        {
            int ret = 0;
            string[] fieldname = { "[BodySize]='", "[Width]='", "[Pitch]='", "[BallDiameter]='", "[BallHeight]='", "[PkgHeight]='" };
            string str = "";
            for (int i = 0; i < num; i++)
            {
                if (i == 0)
                {
                    str += " where ";
                }
                str += fieldname[posilist[i] - 1];
                str += valist[i].ToString();
                if (i != num - 1)
                {
                    str += "' and ";
                }
                else
                {
                    str += "'";
                }
            }

            string[] socketlist = new string[2000];
            int n = 0;
            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            OleDbCommand odcom = odconn.CreateCommand();
            odcom.CommandText = "select * from HWParamTable" + str;
            OleDbDataReader oddrd = odcom.ExecuteReader();

            while (oddrd.Read())
            {
                //if (int.Parse(oddrd[oddrd.GetName(0)].ToString()) == adapterid)
                //{
                //    ret = true;
                //    break;
                //}
                socketlist[n] = oddrd[oddrd.GetName(0)].ToString();
                n++;
            }

            oddrd.Close();
            odconn.Close();

            // get adapter list from the adapter list
            ret = QueryAdapter4SocketId(ref adapterlist, socketlist, n);

            return ret;
        }

        // query notes with pinmapid
        public int QueryNote4PinMapId(ref string[] notelist, int pinmapid)
        {
            int ret = 0;
            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            OleDbCommand odcom = odconn.CreateCommand();
            odcom.CommandText = "select * from NoteListTable";
            OleDbDataReader oddrd = odcom.ExecuteReader();

            while (oddrd.Read())
            {
                if (int.Parse(oddrd[oddrd.GetName(0)].ToString()) == pinmapid)
                {
                    notelist[ret] = oddrd[oddrd.GetName(2)].ToString();
                    ret++;
                }                
            }

            oddrd.Close();
            odconn.Close();

            return ret;
        }

        // query Adapter with socketid[]
        public int QueryAdapter4SocketId(ref int[] adapterlist, string[] socketid, int num)
        {
            int ret = 0;
            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            OleDbCommand odcom = odconn.CreateCommand();
            odcom.CommandText = "select * from AdapterListTable";
            OleDbDataReader oddrd = odcom.ExecuteReader();

            while (oddrd.Read())
            {
                for (int i = 0; i < num; i++)
                {
                    if (oddrd[oddrd.GetName(2)].ToString() == socketid[i])
                    {
                        adapterlist[ret] = int.Parse(oddrd[oddrd.GetName(0)].ToString());
                        ret++;
                        i = num;
                    }
                }
            }

            oddrd.Close();
            odconn.Close();

            return ret;
        }

        // Load Socket with socketid
        public HWParamTableElement LoadSocket4Id(string socketid)
        {
            HWParamTableElement hwpte = new HWParamTableElement();
            bool ret = false;

            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            OleDbCommand odcom = odconn.CreateCommand();
            odcom.CommandText = "select * from HWParamTable";
            OleDbDataReader oddrd = odcom.ExecuteReader();

            while (oddrd.Read())
            {
                if (oddrd[oddrd.GetName(0)].ToString() == socketid)
                {
                    hwpte.SocketId = socketid;
                    hwpte.BodySize = double.Parse(oddrd[oddrd.GetName(1)].ToString());
                    hwpte.Width = double.Parse(oddrd[oddrd.GetName(2)].ToString());
                    hwpte.Pitch = double.Parse(oddrd[oddrd.GetName(3)].ToString());
                    hwpte.BallDiameter = double.Parse(oddrd[oddrd.GetName(4)].ToString());
                    hwpte.BallHeight = double.Parse(oddrd[oddrd.GetName(5)].ToString());
                    hwpte.PkgHeight = double.Parse(oddrd[oddrd.GetName(6)].ToString());

                    ret = true;
                    break;
                }
            }

            oddrd.Close();
            odconn.Close();

            if (!ret)
            { 
                // don't find
                return null;
            }

            return hwpte;
        }

        // query PinMapList with pinmapid
        public int QueryPinMap4PinMapListTable(ref int[] pinmaplist, int[] posilist, string[] valist, int num)
        {
            int ret = 0;
            string[] fieldname = { "[PackageName]='", "RowNum=", "ColumnNum="};
            string str = "";
            for (int i = 0; i < num; i++)
            {
                if (i == 0)
                {
                    str += " where ";
                }
                str += fieldname[posilist[i] - 1];
                if (i == 0)
                {
                    str += valist[i];                    
                }
                else
                {
                    str += int.Parse(valist[i]);                    
                }

                if (i != num - 1)
                {
                    if (posilist[i] == 1)
                    {
                        str += "' and ";
                    }
                    else
                    {
                        str += " and ";
                    }
                }
                else
                {
                    if (posilist[i] - 1 == 0)
                        str += "'";
                }
            }
            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            OleDbCommand odcom = odconn.CreateCommand();
            odcom.CommandText = "select * from PinMapListTable" + str;
            OleDbDataReader oddrd = odcom.ExecuteReader();

            while (oddrd.Read())
            {
                //if (int.Parse(oddrd[oddrd.GetName(0)].ToString()) == adapterid)
                //{
                //    ret = true;
                //    break;
                //}
                pinmaplist[ret] = int.Parse(oddrd[oddrd.GetName(0)].ToString());
                ret++;
            }

            oddrd.Close();
            odconn.Close();

            return ret;
        }

        // query AdapterListTable with adapterid
        public int QueryAdapter4AdapterListTable(ref int[] adapterlist, int[] pinmaplist, int num)
        {
            int ret = 0;

            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            OleDbCommand odcom = odconn.CreateCommand();
            odcom.CommandText = "select * from AdapterListTable";
            OleDbDataReader oddrd = odcom.ExecuteReader();

            while (oddrd.Read())
            {
                for (int i = 0; i < num; i++)
                {
                    if (int.Parse(oddrd[oddrd.GetName(1)].ToString()) == pinmaplist[i])
                    {
                        adapterlist[ret] = int.Parse(oddrd[oddrd.GetName(0)].ToString());
                        ret++;
                        i = num;
                    }
                }
            }

            oddrd.Close();
            odconn.Close();

            return ret;
        }

        // query Adapter with pinmapid
        public int QueryAdapter4PinMapId(ref AdapterListTableElement[] alte, int pinmaid)
        {
            int ret = 0;
            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            OleDbCommand odcom = odconn.CreateCommand();
            odcom.CommandText = "select * from AdapterListTable";
            OleDbDataReader oddrd = odcom.ExecuteReader();

            while (oddrd.Read())
            {
                if (int.Parse(oddrd[oddrd.GetName(1)].ToString()) == pinmaid)
                {
                    alte[ret] = new AdapterListTableElement();
                    alte[ret].AdapterId = int.Parse(oddrd[oddrd.GetName(0)].ToString());
                    alte[ret].PinMapId = pinmaid;
                    alte[ret].SocketId = oddrd[oddrd.GetName(2)].ToString();
                    alte[ret].AdapterName = oddrd[oddrd.GetName(3)].ToString();
                    alte[ret].SocketNum = int.Parse(oddrd[oddrd.GetName(4)].ToString());
                    alte[ret].PinOneOri = oddrd[oddrd.GetName(5)].ToString();
                    alte[ret].PackageInfo = oddrd[oddrd.GetName(6)].ToString();
                    ret++;
                }                
            }

            oddrd.Close();
            odconn.Close();

            return ret;
        }

        // query exist with user
        public bool QueryUserListTable(string user)
        {
            bool ret = false;
            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            OleDbCommand odcom = odconn.CreateCommand();
            odcom.CommandText = "select * from UserListTable";
            OleDbDataReader oddrd = odcom.ExecuteReader();

            while (oddrd.Read())
            {
                if (oddrd[oddrd.GetName(0)].ToString() == user)
                {
                    ret = true;
                    break;
                }
            }

            oddrd.Close();
            odconn.Close();

            return ret;
        }

        // Query Option list with adatperid
        public int QueryOption4AdapterId(ref string[] posilist, ref int[] optionlist, int adapterid)
        {
            int ret = 0;

            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            OleDbCommand odcom = odconn.CreateCommand();
            odcom.CommandText = "select * from AdapterOptionIdTable";
            OleDbDataReader oddrd = odcom.ExecuteReader();

            while (oddrd.Read())
            {
                if (adapterid == int.Parse(oddrd[oddrd.GetName(0)].ToString()))
                {
                    posilist[ret] = oddrd[oddrd.GetName(1)].ToString();
                    optionlist[ret] = int.Parse(oddrd[oddrd.GetName(2)].ToString());
                    ret++;
                }
            }

            oddrd.Close();
            odconn.Close();

            return ret;
        }

        // Load UserList
        public UserListTableElement LoadUserListTableElement(string user)
        {
            UserListTableElement ulte = new UserListTableElement();
            ulte.User = user;
            
            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            OleDbCommand odcom = odconn.CreateCommand();
            odcom.CommandText = "select * from UserListTable";
            OleDbDataReader oddrd = odcom.ExecuteReader();

            while (oddrd.Read())
            {
                if (user == oddrd[oddrd.GetName(0)].ToString())
                {
                    ulte.Password = oddrd[oddrd.GetName(1)].ToString();
                    ulte.Privilege = int.Parse(oddrd[oddrd.GetName(2)].ToString());
                    break;
                }
            }

            oddrd.Close();
            odconn.Close();

            return ulte;
        }

        // update record to UserListTable
        public void UpdateRecUserListTable(UserListTableElement ulte)
        {
            OleDbConnection odconn = new OleDbConnection(constr);
            odconn.Open();

            string udstr = "update UserListTable set [Password]='" + ulte.Password + "' where [User]='" + ulte.User + "'";
            OleDbCommand odcom = new OleDbCommand(udstr, odconn);
            odcom.ExecuteNonQuery();

            odconn.Close();
        }
	}
}